package bean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Enumeration;

import javax.swing.text.StyledEditorKit.BoldAction;

import com.oreilly.servlet.MultipartRequest;
import com.oreilly.servlet.multipart.DefaultFileRenamePolicy;

public class ScoreDTO {
	private ScoreVo vo;
	private Connection conn;
	int size= 1024*1024*10;
	String saveDir="C:/Users/jhta/Desktop/workspace0/1406_web/WebContent/memberPhoto/";
	String oriFile="";
	String sysFile="";
	//페이지와 관련된 변수
	int listSize=5;		//페이지당 표시되는 행수
	int blockSize=3;	//한블럭당 표시되는 페이지수
	int totSize=0;		//전체 게시물의 갯수
	int totPage=0;		//전체 페이지수
	int totBlock=0;		//전체 블럭수
	int nowPage=1;		//현재 페이지
	int nowBlock=1;		//현재 블럭
	int startNo=0;		//리스트 목록의 시작 위치
	int endNo=0;		//리스트 목록의 끝 위치
	int startPage=0;	//현재블럭에서 표시되는 시작 블럭
	int endPage=0;		//현재 블럭에서 표시되는 끝 블럭 번호
	
	
	
	public ScoreDTO(){
		conn = new MyOracle().getConn();
	}
	public boolean insert(){
		boolean b = false;
		String sql = "insert into score(mid,grade,exam,point,photo,rno)values(?,?,?,?,?,seq_score_rno.nextval)";
		try{
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, vo.getMid());
			pst.setString(2, vo.getGrade());
			pst.setString(3, vo.getExam());
			pst.setInt(4, vo.getPoint());
			pst.setString(5, vo.getPhoto());
			int cnt=pst.executeUpdate();
			if(cnt>0)b=true;
			
			pst.close();
			conn.close();
		}catch(Exception ex){
			ex.printStackTrace();
		}
		
		return b;
	}
	public void page(){ //페이지관련 연산
		String sql = "select count(*)cnt from score where mid like ? or exam like ?";
		
		try{
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1,"%"+ vo.getFind()+"%");
			pst.setString(2,"%"+ vo.getFind()+"%");
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				totSize = rs.getInt("cnt");
				//전체 페이지수
				totPage = (int)Math.ceil(totSize*1.0/listSize);
				//전체 블럭수
				totBlock=(int)Math.ceil(totPage*1.0/blockSize);
				nowBlock = (int)Math.ceil(nowPage*1.0/blockSize);
				//페이지의 시작과 끝 번호
				endPage = nowBlock * blockSize;
				startPage = endPage-blockSize+1;
				//게시물의 시작번호 끝번호
				endNo = nowPage * listSize;
				startNo = endNo - listSize+1;
				//현재 블럭
				// endPage, endNo 보정값
				if(endPage>totPage) endPage = totPage;
				if(endNo > totSize) endNo = totSize;
				
			}
			System.out.println("totSize="+totSize);
			System.out.println("<li>totPage="+totPage);
			System.out.println("<li>totBlock="+totBlock);
			System.out.println(endPage);
			System.out.println(startNo);
			System.out.println(startPage);
			System.out.println(endNo);
			
		}catch(Exception ex){
			ex.printStackTrace();
		}	
	}
	public ArrayList<ScoreVo> select(){
		page();
		ArrayList<ScoreVo> al = new ArrayList<>();
		String sql = 
	            "select * from("
	            +"   select rownum rn, c.* from( "
	            +"   select * from score where mid like ? or exam like ?)c"
	            +"   )b "
	            +"where b.rn between ? and ?";
		try{
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, "%" + vo.getFind() + "%");
			pst.setString(2, "%" + vo.getFind() + "%");
			pst.setInt(3,startNo);
			pst.setInt(4, endNo);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				
				ScoreVo v = new ScoreVo();
				v.setExam(rs.getString("exam"));
				v.setRno(rs.getInt("rno"));
				v.setMid(rs.getString("mid"));
				v.setGrade(rs.getString("grade"));
				v.setPoint(rs.getInt("point"));
				al.add(v);
			}
			rs.close();
			pst.close();
			conn.close();
		}catch(Exception ex){
			ex.printStackTrace();
		}

		return al;
		
		
	}
	public ScoreVo selectOne(){
		
		ScoreVo v = new ScoreVo();
		String sql = "select *from score where rno=?";
		
		try{
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setInt(1, vo.getRno());
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				v.setMid(rs.getString("mid"));
				v.setGrade(rs.getString("grade"));
				v.setPoint(rs.getInt("point"));
				v.setExam(rs.getString("exam"));
				v.setRno(rs.getInt("rno"));

				
			}
			rs.close();
			pst.close();
			conn.close();
		}catch(Exception ex){
			ex.printStackTrace();
		}
		
		return v;
		
	}
	public boolean delete(){
		boolean b=false;
		
		String sql = "delete from score where rno=?";
		
		try{
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setInt(1, vo.getRno());
			int cnt = pst.executeUpdate();
			if(cnt>0)b=true;
			
			pst.close();
			conn.close();
		}catch(Exception ex){
			ex.printStackTrace();
		}
		
		return b;
		
		
	}
	public ScoreVo getVo() {
		return vo;
	}
	public void setVo(ScoreVo vo) {
		this.vo = vo;
		MultipartRequest multi = null;
		this.nowPage = vo.getNowPage();
		this.nowBlock = vo.getNowBlock();
		try{
			multi = new MultipartRequest(vo.getRequest(),saveDir,size,"utf-8",new DefaultFileRenamePolicy());
			Enumeration files = multi.getFileNames();
			String tempFile = (String)files.nextElement();
			oriFile = multi.getOriginalFileName(tempFile);
			sysFile = multi.getFilesystemName(tempFile);
			
			vo.setMid(multi.getParameter("mid"));
			vo.setGrade(multi.getParameter("grade"));
			vo.setExam(multi.getParameter("exam"));
			vo.setPhoto(sysFile);
			
			//database저장
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}
	public int getListSize() {
		return listSize;
	}
	public void setListSize(int listSize) {
		this.listSize = listSize;
	}
	public int getBlockSize() {
		return blockSize;
	}
	public void setBlockSize(int blockSize) {
		this.blockSize = blockSize;
	}
	public int getTotSize() {
		return totSize;
	}
	public void setTotSize(int totSize) {
		this.totSize = totSize;
	}
	public int getTotPage() {
		return totPage;
	}
	public void setTotPage(int totPage) {
		this.totPage = totPage;
	}
	public int getTotBlock() {
		return totBlock;
	}
	public void setTotBlock(int totBlock) {
		this.totBlock = totBlock;
	}
	public int getNowPage() {
		return nowPage;
	}
	public void setNowPage(int nowPage) {
		this.nowPage = nowPage;
	}
	public int getNowBlock() {
		return nowBlock;
	}
	public void setNowBlock(int nowBlock) {
		this.nowBlock = nowBlock;
	}
	public int getStartNo() {
		return startNo;
	}
	public void setStartNo(int startNo) {
		this.startNo = startNo;
	}
	public int getEndNo() {
		return endNo;
	}
	public void setEndNo(int endNo) {
		this.endNo = endNo;
	}
	public int getStartPage() {
		return startPage;
	}
	public void setStartPage(int startPage) {
		this.startPage = startPage;
	}
	public int getEndPage() {
		return endPage;
	}
	public void setEndPage(int endPage) {
		this.endPage = endPage;
	}
	
	
}
